import pymysql
import datetime
from openpyxl import *
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Border, Side, Alignment
today = datetime.date.today()
yesterday = today - datetime.timedelta(1)
infos = ["订阅号","服务号"]
info = ["男","女"]


fill_date = PatternFill(
    fill_type='solid', start_color='D9FDE900', end_color='D9FDE900')
fill_main = PatternFill(
    fill_type='solid', start_color='C6FDCC00', end_color='C6FDCC00')
fill_alt = PatternFill(
    fill_type='solid', start_color='BCD7E400', end_color='BCD7E400')

thin = Side(border_style="thin", color="000000")
border_normal = Border(top=thin, bottom=thin, left=thin, right=thin)
align_header = Alignment(horizontal='center')
align_header_v = Alignment(vertical='center', wrap_text=True)


def set_columns_width(sheet, columns_width):
    for index, width in enumerate(columns_width):
        sheet.column_dimensions[get_column_letter(index + 1)].width = width

# 设置单元格格式
def set_cell_format(sheet, column_letters, row_index, number_format):
    for column in column_letters:
        sheet[column + str(row_index)].number_format = number_format


def set_border(rows):
    for row in rows:
        for cell in row:
            cell.border = border_normal


def set_styles(rows, fill=None, alignment=None):
    for row in rows:
        for cell in row:
            if fill:
                cell.fill = fill
            if alignment:
                cell.alignment = alignment

def make_workbook(sheet_name=None):
    workbook = Workbook()
    sheet = workbook.active
    if sheet_name:
        sheet.title = sheet_name
    return workbook, sheet

def save_excel(workbook, name):
    filename = '{}.xlsx'.format(name)
    try:
        workbook.save(filename)
        workbook.close()
    except PermissionError:
        print('无法写入{}，请检查是否在Excel中打开了文件，如有需要请另存为并关闭'.format(filename))

def select_sexs_info(yesterday, sexs):
    conn = pymysql.Connect(
            host='',  # mysql服务器地址
            port=3306,  # mysql服务器端口号
            user='',  # 用户名
            password='',  # 密码
            db='WeChat_backstage_data',  # 数据库名
            charset='utf8mb4'  # 连接编码
    )
    cursor = conn.cursor()
    sql_select = "SELECT * FROM Data_form_table WHERE 时间='{0}' AND 性别 = '{1}'".format(
                    yesterday, sexs)
    # 执行语句

    cursor.execute(sql_select)
    result = cursor.fetchall()
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return result

def select_type_sexs_info(yesterday, sexs, types):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8mb4'  # 连接编码
    )
    cursor = conn.cursor()
    sql_select = "SELECT * FROM Data_form_table WHERE 时间='{0}' AND 性别 = '{1}' AND 类型 = '{2}'".format(
        yesterday, sexs, types)
    # 执行语句

    cursor.execute(sql_select)
    result = cursor.fetchall()
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return result


for y in range(len(info)):
    results = select_sexs_info(yesterday, info[y])
    print("开始生成{0} {1} 总数据表格".format(yesterday,info[y]))
    workbook = Workbook()
    sheet1 = workbook.active
    sheet1.title = "{0} {1}总数据".format(yesterday,info[y])
    sheet1.append(['日期', yesterday])
    set_columns_width(sheet1, [10, 20, 8, 18, 10, 7, 6, 10, 7, 6, 6, 6, 9, 8, 8, 8, 8,
                              8, 8, 8, 8, 8, 8, 8, 8, 14, 14, 14, 8, 8, 10, 10, 8, 7, 8, 10, 10, 8, 8, 8, 8, 30])
    sheet1.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '性别', '男女率(%)', '累计关注', '新增',
                  '取关', '净增', '头条阅读数', '综合阅读人数', '综合阅读次数', '会话阅读人数', '会话阅读次数', '朋友圈阅读人数',
                  '朋友圈阅读次数', '转发人数', '转发次数', '收藏人数', '收藏次数', '历史消息阅读次数', '真实阅读', '头条阅读率(%)',
                  '综合阅读率(%)', '真实阅读率(%)', '二次传播比', '人均打开次数', '转发率(%)', '打开率(%)', '曝光量', '点击量',
                  '总收入', '曝光率(%)', '点击率(%)', '曝光单价', '点击单价', '阅读单价', '粉丝单价', '主体公司'])
    row_index = 2
    for dic in results:
        dic = list(dic)
        for u in range(8,42):
            if dic[u] != "-" and dic[u] != "当天未发" and dic[u] != "None" and dic[u] != "" and dic[u] != "非图文类型":
                if "%" in str(dic[u]):
                    dic[u] = round(float(str(dic[u]).strip('%')),2)
                if str(dic[1]) == "None":
                    dic[1] = ''
                dic[u] = round(float(dic[u]),4)
        try:
            row_index += 1
            list2 = [dic[1], dic[2], dic[3], dic[4], dic[5], dic[6], dic[7], dic[8], dic[9], dic[10]
                , dic[11], dic[12], dic[13], dic[14], dic[15], dic[16], dic[17], dic[18], dic[19], dic[20], dic[21]
                , dic[22], dic[23], dic[24], dic[25], dic[26], dic[27], dic[28], dic[29], dic[30], dic[31], dic[32]
                , dic[33], dic[34], dic[35], dic[36], dic[37], dic[38], dic[39], dic[40], dic[41], dic[42]]
            sheet1.append(list2)
        except:
            print(dic)
            with open("{0}错误日志.log".format(str(today)), "a", encoding="utf-8") as f:
                f.write(str(dic) + "\r\n")
    sheet1['A1'].fill = fill_date
    set_styles(sheet1['A2:X2'], fill_main, align_header)
    set_styles(sheet1['Y2:AF2'], fill_alt, align_header)
    set_styles(sheet1['AG2:AI2'], fill_main, align_header)
    set_styles(sheet1['AJ2:AP2'], fill_alt, align_header)
    set_border(sheet1['A2:AP{}'.format(row_index)])
    sheet1.freeze_panes = 'H3'
    save_excel(workbook, '{0} {1} 数据总表格'.format(yesterday,info[y]))

    i = 1
    for a in range(len(infos)):
        results = select_type_sexs_info(yesterday, info[y], infos[a])
        print("开始生成{0} {1} {2} 粉丝数据表格".format(yesterday, info[y], infos[a]))
        sheet2 = workbook.create_sheet( index= i,title='粉丝-{0}'.format(infos[a]))
        i += 1
        sheet2.append(['日期', yesterday])
        set_columns_width(sheet2, [10, 20, 8, 18, 10, 7, 6, 10, 10, 8, 8, 8])
        sheet2.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '性别', '男女率(%)', '累计关注', '新增',
                       '取关', '净增'])
        row_index = 2
        for dic in results:
            dic = list(dic)
            for u in range(8, 13):
                if dic[u] != "-" and dic[u] != "当天未发" and dic[u] != "None" and dic[u] != "" and dic[u] != "非图文类型":
                    if "%" in str(dic[u]):
                        dic[u] = round(float(str(dic[u]).strip('%')), 2)
                    if str(dic[1]) == "None":
                        dic[1] = ''
                    dic[u] = round(float(dic[u]), 4)
            try:
                row_index += 1
                list2 = [dic[1], dic[2], dic[3], dic[4], dic[5], dic[6], dic[7], dic[8], dic[9], dic[10]
                    , dic[11], dic[12]]
                sheet2.append(list2)
            except:
                print(dic)
                with open("{0}错误日志.log".format(str(today)), "a", encoding="utf-8") as f:
                    f.write(str(dic) + "\r\n")
        sheet2['A1'].fill = fill_date
        set_styles(sheet2['A2:L2'], fill_main, align_header)
        set_border(sheet2['A2:L{}'.format(row_index)])
        sheet2.freeze_panes = 'H3'
        save_excel(workbook, '{0} {1} 数据总表格'.format(yesterday, info[y]))

    i = 3
    for b in range(len(infos)):
        results = select_type_sexs_info(yesterday, info[y], infos[b])
        print("开始生成{0} {1} {2} 阅读数据表格".format(yesterday, info[y], infos[b]))
        sheet3 = workbook.create_sheet(index= i, title='阅读-{0}'.format(infos[b]))
        i += 1
        sheet3.append(['日期', yesterday])
        set_columns_width(sheet3, [10, 20, 8, 18, 10, 7, 6, 10, 7, 12, 12, 12, 9, 8, 8, 8, 8,
                                   8, 8, 8, 8, 8, 8, 8, 10, 12, 12, 12, 12])
        sheet3.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '性别', '男女率(%)', '累计关注',
                       '头条阅读率(%)', '综合阅读率(%)', '真实阅读率(%)',
                       '头条阅读数', '综合阅读人数', '综合阅读次数', '会话阅读人数', '会话阅读次数', '朋友圈阅读人数',
                       '朋友圈阅读次数', '转发人数', '转发次数', '收藏人数', '收藏次数', '历史消息阅读次数', '真实阅读',
                       '二次传播比', '人均打开次数', '转发率(%)', '打开率(%)'])
        row_index = 2
        for dic in results:
            dic = list(dic)
            for u in range(8, 33):
                if dic[u] != "-" and dic[u] != "当天未发" and dic[u] != "None" and dic[u] != "" and dic[u] != "非图文类型":
                    if "%" in str(dic[u]):
                        dic[u] = round(float(str(dic[u]).strip('%')), 2)
                    if str(dic[1]) == "None":
                        dic[1] = ''
                    dic[u] = round(float(dic[u]), 4)
            try:
                row_index += 1
                list2 = [dic[1], dic[2], dic[3], dic[4], dic[5], dic[6], dic[7], dic[8], dic[9],
                         dic[26], dic[27], dic[28],
                         dic[13], dic[14], dic[15], dic[16], dic[17], dic[18], dic[19], dic[20], dic[21],
                         dic[22], dic[23], dic[24], dic[25], dic[29], dic[30], dic[31], dic[32]]
                sheet3.append(list2)
            except:
                print(dic)
                with open("{0}错误日志.log".format(str(today)), "a", encoding="utf-8") as f:
                    f.write(str(dic) + "\r\n")
        sheet3['A1'].fill = fill_date
        set_styles(sheet3['A2:X2'], fill_main, align_header)
        set_styles(sheet3['Y2:AC2'], fill_alt, align_header)
        set_border(sheet3['A2:AC{}'.format(row_index)])
        sheet3.freeze_panes = 'H3'
        save_excel(workbook, '{0} {1} 数据总表格'.format(yesterday, info[y]))

    i = 5
    for c in range(len(infos)):
        results = select_type_sexs_info(yesterday, info[y], infos[c])
        print("开始生成{0} {1} {2} 流量主数据表格".format(yesterday, info[y], infos[c]))
        sheet4 = workbook.create_sheet(index= i, title='流量主-{0}'.format(infos[c]))
        i += 1
        sheet4.append(['日期', yesterday])
        set_columns_width(sheet4, [10, 20, 8, 18, 10, 7, 6, 10, 10, 9, 9, 9, 9, 10, 10, 9, 9, 9, 9])
        sheet4.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '性别', '男女率(%)', '累计关注', '综合阅读次数',
                       '曝光量', '点击量', '总收入', '曝光率(%)', '点击率(%)', '曝光单价', '点击单价', '阅读单价', '粉丝单价'])
        row_index = 2
        for dic in results:
            dic = list(dic)
            for u in range(8, 42):
                if dic[u] != "-" and dic[u] != "当天未发" and dic[u] != "None" and dic[u] != "" and dic[u] != "非图文类型":
                    if "%" in str(dic[u]):
                        dic[u] = round(float(str(dic[u]).strip('%')), 2)
                    if str(dic[1]) == "None":
                        dic[1] = ''
                    dic[u] = round(float(dic[u]), 4)
            try:
                row_index += 1
                list2 = [dic[1], dic[2], dic[3], dic[4], dic[5], dic[6], dic[7], dic[8], dic[9],
                         dic[15], dic[33], dic[34], dic[35], dic[36], dic[37], dic[38], dic[39],
                         dic[40], dic[41]]
                sheet4.append(list2)
            except:
                print(dic)
                with open("{0}错误日志.log".format(str(today)), "a", encoding="utf-8") as f:
                    f.write(str(dic) + "\r\n")
        sheet4['A1'].fill = fill_date
        set_styles(sheet4['A2:S2'], fill_main, align_header)
        set_border(sheet4['A2:S{}'.format(row_index)])
        sheet4.freeze_panes = 'H3'
        save_excel(workbook, '{0} {1} 数据总表格'.format(yesterday, info[y]))